package com.ruicar.afs.cloud.batch.dayend.process;

import cn.hutool.core.date.DateUnit;
import cn.hutool.core.date.DateUtil;
import com.alibaba.fastjson.JSONObject;
import com.ruicar.afs.cloud.batch.dayend.handle.run.SystemJdbcConfig;
import com.ruicar.afs.cloud.batch.dayend.vo.DebitRecordVo;
import com.ruicar.afs.cloud.batch.frame.AbsBatchRunner;
import com.ruicar.afs.cloud.common.core.exception.AfsBaseException;
import com.ruicar.afs.cloud.common.core.uid.UidGenerator;
import com.ruicar.afs.cloud.common.core.util.SpringContextHolder;
import com.ruicar.afs.cloud.common.modules.contract.enums.DebitStatusEnum;
import com.ruicar.afs.cloud.common.modules.contract.enums.FeeTypeEnum;
import com.ruicar.afs.cloud.common.modules.contract.enums.ReceiptSourceTypeEnum;
import com.ruicar.afs.cloud.common.util.EmptyUtils;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * <p>Description: 每日生成待扣款数据</p>
 *
 * @author ZC.GUO
 * @version 1.0
 * @date create on 2020-08-25 10:04
 */
public class DebitRecordGenerateBatchRunner extends AbsBatchRunner {

    public static final int TOTAL_NUM = 300;
    private UidGenerator uidGenerator = SpringContextHolder.getBean(UidGenerator.class);
    private SystemJdbcConfig systemJdbcConfig= SpringContextHolder.getBean(SystemJdbcConfig.class);

    @Override
    public void process(String param) {
        Connection base = this.getConnection();
        String breakPointString = loadBreakPoint();
        Connection contract = this.getConnection(systemJdbcConfig.getBasic());
        Connection manage = this.getConnection(systemJdbcConfig.getManage());
        try{
                /**  生产并保存待扣款数据集合 by ZC.GUO  **/
            this.collectRecordList(contract,manage,breakPointString);
        }catch (Exception e){
            logger.error("",e);
            throw new AfsBaseException(e.getMessage());
        }finally {
            clean(contract);
            clean(manage);
            clean(base);
        }
    }

    public void collectRecordList(Connection contract,Connection manage,String breakPointString){
        /**  加载所有银行卡数据 by ZC.GUO  **/
        Map<String,Map<String,String>> allCard = this.getAllCard(contract);
        /**  提前还款扣款 by ZC.GUO  **/
        List<String> expectContractNoList = this.genDebitRecordForPrepayment(contract,manage,breakPointString,allCard);
        /**  还款计划扣款 by ZC.GUO  **/
        this.genDebitRecordForRepayPlan(contract,expectContractNoList,breakPointString,allCard);
        /**  费用和罚息扣款 by ZC.GUO  **/
        this.genDebitRecordForExpense(contract,expectContractNoList,breakPointString,allCard);
    }

    public void genDebitRecordForExpense(Connection contract,List<String> expectContractNoList,String breakPointString,Map<String,Map<String,String>> allCard) {
        /**  断点 by ZC.GUO  **/
        JSONObject breakPoint = null;
        if (EmptyUtils.isNotEmpty(breakPointString)){
            breakPoint = JSONObject.parseObject(breakPointString);
        }

        StringBuffer baseQuerySql = new StringBuffer();
        baseQuerySql.append("SELECT");
        baseQuerySql.append("	exp.contract_no, exp.cost_type, 'basic_expense_record' source_table, exp.id source_id, exp.remaining_amt");
        baseQuerySql.append(" FROM basic_expense_record exp");
        baseQuerySql.append(" WHERE exp.`status` = 'unsettle' and exp.del_flag = '0'");
        if (EmptyUtils.isNotEmpty(expectContractNoList)){
            baseQuerySql.append(" AND exp.contract_no not in (");
            for (int i = 0; i < expectContractNoList.size() ; i++){
                if(i==0){
                    baseQuerySql.append("?");
                }else{
                    baseQuerySql.append(",?");
                }
            }
            baseQuerySql.append(")");
        }

        /**  添加断点，提前还款和还款计划的断点不对费用的查询产生影响 by ZC.GUO  **/
        if (EmptyUtils.isNotEmpty(breakPoint)){
            /**  费用的断点，则从断点处开始查询 by ZC.GUO  **/
            if ("expense".equals(breakPoint.getString("key"))){
                baseQuerySql.append(" and exp.id > " + breakPoint.getString("value"));
            }
        }
        baseQuerySql.append(" order by exp.id asc");

        /**  查询总数量 by ZC.GUO  **/
        StringBuffer countSql = new StringBuffer();
        countSql.append("select count(temp.source_id) totalNum from ( ");
        countSql.append(baseQuerySql.toString());
        countSql.append(" ) temp ");
        PreparedStatement countPs = null;
        ResultSet countRs = null;
        int totalNum = 0;

        /**  分页查询sql by ZC.GUO  **/
        StringBuffer expenseRecordQuerySql = new StringBuffer();
        expenseRecordQuerySql.append(baseQuerySql.toString());
        expenseRecordQuerySql.append(" LIMIT ?,?");
        PreparedStatement expenseRecordQueryPs = null;
        ResultSet expenseRecordResultSet = null;

        try {
            countPs = contract.prepareStatement(countSql.toString());
            if (EmptyUtils.isNotEmpty(expectContractNoList)){
                for (int i = 0; i < expectContractNoList.size() ; i++){
                    countPs.setString(i+1,expectContractNoList.get(i));
                }
            }
            countRs = countPs.executeQuery();
            if(EmptyUtils.isNotEmpty(countRs) && countRs.next()){
                totalNum = countRs.getInt("totalNum");
            }

            if (totalNum>0){
                for (int i=0;i*TOTAL_NUM<totalNum;i++){
                    List<DebitRecordVo> debitRecordVoList = new ArrayList<>();
                    expenseRecordQueryPs = contract.prepareStatement(expenseRecordQuerySql.toString());
                    if (EmptyUtils.isNotEmpty(expectContractNoList)){
                        for (int j = 0; j < expectContractNoList.size() ; j++){
                            expenseRecordQueryPs.setString(j+1,expectContractNoList.get(j));
                        }
                    }
                    expenseRecordQueryPs.setInt(EmptyUtils.isNotEmpty(expectContractNoList)?expectContractNoList.size()+1:1,i*TOTAL_NUM);
                    expenseRecordQueryPs.setInt(EmptyUtils.isNotEmpty(expectContractNoList)?expectContractNoList.size()+2:2,TOTAL_NUM);
                    expenseRecordResultSet = expenseRecordQueryPs.executeQuery();
                    while(EmptyUtils.isNotEmpty(expenseRecordResultSet) && expenseRecordResultSet.next()){
                        /**  合同号 by ZC.GUO  **/
                        String contractNo = expenseRecordResultSet.getString("contract_no");;
                        /**  费用类型 by ZC.GUO  **/
                        String costType = expenseRecordResultSet.getString("cost_type");
                        /**  扣款表 by ZC.GUO  **/
                        String sourceTable = expenseRecordResultSet.getString("source_table");
                        /**  表id by ZC.GUO  **/
                        Long sourceId = expenseRecordResultSet.getLong("source_id");
                        /**  金额 by ZC.GUO  **/
                        BigDecimal remainingAmt = expenseRecordResultSet.getBigDecimal("remaining_amt");;

                        /**  本金 by ZC.GUO  **/
                        DebitRecordVo expenseDebitRecord = this.buildDebitRecord(contractNo,costType,sourceTable,sourceId,remainingAmt,null,null);
                        /**  完善扣款银行卡信息 by ZC.GUO  **/
                        expenseDebitRecord = this.fillCardInfo(expenseDebitRecord,allCard);

                        /**  没有银行卡信息的不扣款 by ZC.GUO  **/
                        if (EmptyUtils.isNotEmpty(expenseDebitRecord.getDebitAccount())&&expenseDebitRecord.getReadyAmt().compareTo(BigDecimal.ZERO)>0){
                            debitRecordVoList.add(expenseDebitRecord);
                        }
                    }
                    if (EmptyUtils.isNotEmpty(debitRecordVoList)){
                        this.saveRecordList(debitRecordVoList,contract);
                        saveBreakPoint(this.buildBreakPointInfo("expense",debitRecordVoList.get(debitRecordVoList.size()-1).getSourceId().toString()).toJSONString());
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        }finally{
            clean(expenseRecordQueryPs);
            clean(expenseRecordResultSet);
        }
    }
    public void genDebitRecordForRepayPlan(Connection contract,List<String> expectContractNoList,String breakPointString,Map<String,Map<String,String>> allCard) {
        /**  断点 by ZC.GUO  **/
        JSONObject breakPoint = null;
        if (EmptyUtils.isNotEmpty(breakPointString)){
            breakPoint = JSONObject.parseObject(breakPointString);
        }

        StringBuffer baseQuerySql = new StringBuffer();
        baseQuerySql.append("SELECT");
        baseQuerySql.append(" plan.contract_no, 'basic_repayment_plan' source_table, plan.id source_id, plan.receivable_principle,");
        baseQuerySql.append(" IFNULL(plan.actual_principle, 0) actual_principle, plan.receivable_interest, ");
        baseQuerySql.append(" IFNULL(plan.actual_interest, 0) actual_interest, plan.term_no, plan.due_date");
        baseQuerySql.append(" FROM basic_repayment_plan plan");
        baseQuerySql.append(" WHERE plan.`status` IN ('UNPAID', 'OVERDUE') and plan.del_flag = '0'");
        baseQuerySql.append(" AND plan.due_date <= STR_TO_DATE( CONCAT( DATE_FORMAT(?, '%Y-%m-%d'), ' 23:59:59' ), '%Y-%m-%d %H:%i:%s' )");
        if (EmptyUtils.isNotEmpty(expectContractNoList)){
            baseQuerySql.append(" AND plan.contract_no not in (");
            for (int i = 0; i < expectContractNoList.size() ; i++){
                if(i==0){
                    baseQuerySql.append("?");
                }else{
                    baseQuerySql.append(",?");
                }
            }
            baseQuerySql.append(")");
        }
        /**  添加断点(提前结清的断点不对还款计划产生影响 by ZC.GUO  **/
        if (EmptyUtils.isNotEmpty(breakPoint)){
            /**  是还款计划的断点，则从断点开始查询 by ZC.GUO  **/
            if ("plan".equals(breakPoint.getString("key"))){
                baseQuerySql.append(" and plan.id > " + breakPoint.getString("value"));
            }
            /**  是费用的断点，则还款计划的所有数据都查询不到 by ZC.GUO  **/
            else if ("expense".equals(breakPoint.getString("key"))){
                baseQuerySql.append(" and plan.id is null ");
            }
        }
        baseQuerySql.append(" order by plan.id asc");

        /**  查询总数量 by ZC.GUO  **/
        StringBuffer countSql = new StringBuffer();
        countSql.append("select count(temp.source_id) totalNum from ( ");
        countSql.append(baseQuerySql.toString());
        countSql.append(" ) temp ");
        PreparedStatement countPs = null;
        ResultSet countRs = null;
        int totalNum = 0;

        /**  分页查询sql by ZC.GUO  **/
        StringBuffer repayPlanRecordQuerySql = new StringBuffer();
        repayPlanRecordQuerySql.append(baseQuerySql.toString());
        repayPlanRecordQuerySql.append(" LIMIT ?,?");
        PreparedStatement repayPlanRecordQueryPs = null;
        ResultSet repayPlanRecordResultSet = null;

        try {

            countPs = contract.prepareStatement(countSql.toString());
            countPs.setDate(1,new Date(batchDateInfo.getBatchDate().getTime()));
            if (EmptyUtils.isNotEmpty(expectContractNoList)){
                for (int i = 0; i < expectContractNoList.size() ; i++){
                    countPs.setString(i+2,expectContractNoList.get(i));
                }
            }
            countRs = countPs.executeQuery();
            if(EmptyUtils.isNotEmpty(countRs) && countRs.next()){
                totalNum = countRs.getInt("totalNum");
            }

            if (totalNum>0){
                for (int i=0;i*TOTAL_NUM<totalNum;i++){
                    List<DebitRecordVo> debitRecordVoList = new ArrayList<>();
                    repayPlanRecordQueryPs = contract.prepareStatement(repayPlanRecordQuerySql.toString());
                    repayPlanRecordQueryPs.setDate(1,new Date(batchDateInfo.getBatchDate().getTime()));
                    if (EmptyUtils.isNotEmpty(expectContractNoList)){
                        for (int j = 0; j < expectContractNoList.size() ; j++){
                            repayPlanRecordQueryPs.setString(j+2,expectContractNoList.get(j));
                        }
                    }
                    repayPlanRecordQueryPs.setInt(EmptyUtils.isNotEmpty(expectContractNoList)?expectContractNoList.size()+2:2,i*TOTAL_NUM);
                    repayPlanRecordQueryPs.setInt(EmptyUtils.isNotEmpty(expectContractNoList)?expectContractNoList.size()+3:3,TOTAL_NUM);
                    repayPlanRecordResultSet = repayPlanRecordQueryPs.executeQuery();
                    while(EmptyUtils.isNotEmpty(repayPlanRecordResultSet) && repayPlanRecordResultSet.next()){
                        /**  合同号 by ZC.GUO  **/
                        String contractNo = repayPlanRecordResultSet.getString("contract_no");;
                        /**  扣款表 by ZC.GUO  **/
                        String sourceTable = repayPlanRecordResultSet.getString("source_table");
                        /**  表id by ZC.GUO  **/
                        Long sourceId = repayPlanRecordResultSet.getLong("source_id");
                        /**  应收本金 by ZC.GUO  **/
                        BigDecimal receivablePrinciple = repayPlanRecordResultSet.getBigDecimal("receivable_principle");;
                        /**  实收本金 by ZC.GUO  **/
                        BigDecimal actualPrinciple = repayPlanRecordResultSet.getBigDecimal("actual_principle");
                        /**  应收利息 by ZC.GUO  **/
                        BigDecimal receivableInterest = repayPlanRecordResultSet.getBigDecimal("receivable_interest");
                        /**  实收利息 by ZC.GUO  **/
                        BigDecimal actualInterest = repayPlanRecordResultSet.getBigDecimal("actual_interest");;
                        /**  期数 by ZC.GUO  **/
                        Integer termNo = repayPlanRecordResultSet.getInt("term_no");
                        /**  应还日期 by ZC.GUO  **/
                        Date dueDate = repayPlanRecordResultSet.getDate("due_date");

                        /**  本金 by ZC.GUO  **/
                        DebitRecordVo captialDebitRecord = this.buildDebitRecord(contractNo,FeeTypeEnum.capital.toString(),sourceTable,sourceId,receivablePrinciple.subtract(actualPrinciple),dueDate,termNo);
                        /**  完善扣款银行卡信息 by ZC.GUO  **/
                        captialDebitRecord = this.fillCardInfo(captialDebitRecord,allCard);

                        /**  逾期天数 by ZC.GUO  **/
                        captialDebitRecord.setOverdueDays(BigDecimal.valueOf(DateUtil.between(captialDebitRecord.getPlanDate(),batchDateInfo.getBatchDate(), DateUnit.DAY)).intValue());
                        /**  没有银行卡信息的不扣款 by ZC.GUO  **/
                        if (EmptyUtils.isNotEmpty(captialDebitRecord.getDebitAccount())&&captialDebitRecord.getReadyAmt().compareTo(BigDecimal.ZERO)>0){
                            debitRecordVoList.add(captialDebitRecord);
                        }
                        /**  利息 by ZC.GUO  **/
                        DebitRecordVo interestDebitRecord = this.buildDebitRecord(contractNo,FeeTypeEnum.interest.toString(),sourceTable,sourceId,receivableInterest.subtract(actualInterest),dueDate,termNo);
                        /**  完善扣款银行卡信息 by ZC.GUO  **/
                        interestDebitRecord = this.fillCardInfo(interestDebitRecord,allCard);
                        /**  逾期天数 by ZC.GUO  **/
                        interestDebitRecord.setOverdueDays(BigDecimal.valueOf(DateUtil.between(interestDebitRecord.getPlanDate(),batchDateInfo.getBatchDate(), DateUnit.DAY)).intValue());
                        /**  没有银行卡信息的不扣款 by ZC.GUO  **/
                        if (EmptyUtils.isNotEmpty(interestDebitRecord.getDebitAccount())&&interestDebitRecord.getReadyAmt().compareTo(BigDecimal.ZERO)>0){
                            debitRecordVoList.add(interestDebitRecord);
                        }

                    }
                    if (EmptyUtils.isNotEmpty(debitRecordVoList)){
                        this.saveRecordList(debitRecordVoList,contract);
                        saveBreakPoint(this.buildBreakPointInfo("plan",debitRecordVoList.get(debitRecordVoList.size()-1).getSourceId().toString()).toJSONString());
                    }
                    clean(repayPlanRecordQueryPs);
                    clean(repayPlanRecordResultSet);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        }finally{
            clean(repayPlanRecordQueryPs);
            clean(repayPlanRecordResultSet);
            clean(countPs);
            clean(countRs);
        }
    }

    public List<String> genDebitRecordForPrepayment(Connection contract,Connection manage,String breakPointString,Map<String,Map<String,String>> allCard) {
        /**  收集提前结清合同号 by ZC.GUO  **/
        List<String> expectContractNoList = new ArrayList<>();
        StringBuffer prepaymentContractNoQuerySql = new StringBuffer();
        prepaymentContractNoQuerySql.append("SELECT app.contract_no,quo.receivable_amount");
        prepaymentContractNoQuerySql.append(" FROM manage_prepayment_apply app");
        prepaymentContractNoQuerySql.append(" LEFT JOIN manage_prepayment_quotation quo ON app.quotation_id = quo.id");
        prepaymentContractNoQuerySql.append(" WHERE app.apply_status = 'APPROVED' and app.data_source = 'manyKinds' and app.del_flag = '0' and quo.del_flag = '0'");
        prepaymentContractNoQuerySql.append(" order by app.id asc");
        PreparedStatement prepaymentContractNoQueryPs = null;
        ResultSet prepaymentContractNoQuerySet = null;
        try {
            prepaymentContractNoQueryPs = manage.prepareStatement(prepaymentContractNoQuerySql.toString());
            prepaymentContractNoQuerySet = prepaymentContractNoQueryPs.executeQuery();
            while(EmptyUtils.isNotEmpty(prepaymentContractNoQuerySet) && prepaymentContractNoQuerySet.next()){
                /**  合同号 by ZC.GUO  **/
                String contractNo = prepaymentContractNoQuerySet.getString("contract_no");
                expectContractNoList.add(contractNo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        }finally{
            clean(prepaymentContractNoQueryPs);
            clean(prepaymentContractNoQuerySet);
        }

        /**  断点 by ZC.GUO  **/
        JSONObject breakPoint = null;
        if (EmptyUtils.isNotEmpty(breakPointString)){
            breakPoint = JSONObject.parseObject(breakPointString);
        }
        List<DebitRecordVo> debitRecordVoList = new ArrayList<>();
        StringBuffer prepaymentRecordQuerySql = new StringBuffer();
        prepaymentRecordQuerySql.append("SELECT app.contract_no,quo.receivable_amount,app.repay_date,app.repay_mode,'manage_prepayment_apply' source_table,app.id source_id");
        prepaymentRecordQuerySql.append(" FROM manage_prepayment_apply app");
        prepaymentRecordQuerySql.append(" LEFT JOIN manage_prepayment_quotation quo ON app.quotation_id = quo.id");
        prepaymentRecordQuerySql.append(" WHERE app.apply_status = 'APPROVED' and app.data_source = 'manyKinds' and app.del_flag = '0' and quo.del_flag = '0'");
        /**  添加断点 by ZC.GUO  **/
        if (EmptyUtils.isNotEmpty(breakPoint)){
            if ("beforeCharge".equals(breakPoint.getString("key"))){
                prepaymentRecordQuerySql.append(" and app.id > " + breakPoint.getString("value"));
            }
        }
        prepaymentRecordQuerySql.append(" order by app.id asc");


        PreparedStatement prepaymentRecordQueryPs = null;
        ResultSet prepaymentRecordResultSet = null;
        try {
            prepaymentRecordQueryPs = manage.prepareStatement(prepaymentRecordQuerySql.toString());
            prepaymentRecordResultSet = prepaymentRecordQueryPs.executeQuery();
            while(EmptyUtils.isNotEmpty(prepaymentRecordResultSet) && prepaymentRecordResultSet.next()){
                /**  合同号 by ZC.GUO  **/
                String contractNo = prepaymentRecordResultSet.getString("contract_no");;
                /**  应收净额 by ZC.GUO  **/
                BigDecimal receivableAmount = prepaymentRecordResultSet.getBigDecimal("receivable_amount");
                /**  约定还款日 by ZC.GUO  **/
                Date repayDate = prepaymentRecordResultSet.getDate("repay_date");
                /**  还款方式 by ZC.GUO  **/
                String repayMode = prepaymentRecordResultSet.getString("repay_mode");
                /**  扣款表 by ZC.GUO  **/
                String sourceTable = prepaymentRecordResultSet.getString("source_table");
                /**  表id by ZC.GUO  **/
                Long sourceId = prepaymentRecordResultSet.getLong("source_id");

                /**  有提前结清断点则意味着所有的提前结清指令不用重新生成 by ZC.GUO  **/
                if (EmptyUtils.isNotEmpty(breakPoint)&&"beforeCharge".equals(breakPoint.getString("key"))){
                    continue;
                }
                /**  是否需要使用代扣收款 by ZC.GUO  **/
                if (ReceiptSourceTypeEnum.bankDeduct.toString().equals(repayMode)){
                    /**  当日是否为约定扣款日 by ZC.GUO  **/
                    if (DateUtil.isSameDay(batchDateInfo.getBatchDate(),repayDate)){
                        /**  创建扣款记录 by ZC.GUO  **/
                        DebitRecordVo debitRecord = this.buildDebitRecord(contractNo,FeeTypeEnum.advanceSettle.toString(),sourceTable,sourceId,receivableAmount,repayDate,null);
                        /**  完善扣款银行卡信息 by ZC.GUO  **/
                        debitRecord = this.fillCardInfo(debitRecord,allCard);
                        /**  没有银行卡信息的不扣款 待扣款金额大于0的才能扣款 by ZC.GUO  **/
                        if (EmptyUtils.isNotEmpty(debitRecord.getDebitAccount())&& debitRecord.getReadyAmt().compareTo(BigDecimal.ZERO)>0){
                            debitRecordVoList.add(debitRecord);
                        }
                    }
                }

            }
            /**  所有的提前结清扣款指令一次性保存 by ZC.GUO  **/
            if (EmptyUtils.isNotEmpty(debitRecordVoList)){
                this.saveRecordList(debitRecordVoList,contract);
                saveBreakPoint(this.buildBreakPointInfo("beforeCharge",debitRecordVoList.get(debitRecordVoList.size()-1).getSourceId().toString()).toJSONString());
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        }finally{
            clean(prepaymentRecordQueryPs);
            clean(prepaymentRecordResultSet);
        }
        return expectContractNoList;
    }

    public JSONObject buildBreakPointInfo(String key,String value){
        JSONObject point = new JSONObject();
        point.put("key",key);
        point.put("value",value);
        return point;
    }

    public DebitRecordVo buildDebitRecord(String contractNo,String feeType,String sourceTable,Long sourceId,BigDecimal receivableAmount,Date repayDate,Integer termNo){
        DebitRecordVo debitRecord = new DebitRecordVo();
        debitRecord.setId(uidGenerator.getUID());
        debitRecord.setContractNo(contractNo);
        debitRecord.setFeeType(feeType);
        debitRecord.setSourceTable(sourceTable);
        debitRecord.setSourceId(sourceId);
        debitRecord.setReadyAmt(receivableAmount);
        debitRecord.setSucceedAmt(BigDecimal.ZERO);
        debitRecord.setGoingAmt(BigDecimal.ZERO);
        debitRecord.setDebitStatus(DebitStatusEnum.mayDebit.toString());
        debitRecord.setTermNo(termNo);
        debitRecord.setRevision(BigDecimal.ZERO.longValue());
        debitRecord.setWriteOffAmt(BigDecimal.ZERO);
        debitRecord.setPlanDate(repayDate);
        debitRecord.setRecordDate(new Date(batchDateInfo.getBatchDate().getTime()));
        debitRecord.setManualNum(BigDecimal.ZERO.intValue());
        debitRecord.setOverdueDays(BigDecimal.ZERO.intValue());
        return debitRecord;
    }

    public DebitRecordVo fillCardInfo(DebitRecordVo debitRecord,Map<String,Map<String,String>> allCard){
        if (EmptyUtils.isNotEmpty(allCard)){
            Map<String,String> card = allCard.get(debitRecord.getContractNo());
            if (EmptyUtils.isNotEmpty(card)){
                debitRecord.setDebitSignBank(card.get("bank_code"));
                debitRecord.setDebitSignCode(card.get("ver_code"));
                debitRecord.setDebitAccount(card.get("account_no"));
                debitRecord.setDebitAccountName(card.get("account_name"));
                debitRecord.setDebitCardPhone(card.get("bank_phone"));
                debitRecord.setDebitBankBranch(card.get("bank_branch"));
                debitRecord.setDebitCertNo(card.get("cert_no"));
            }
        }
        return debitRecord;
    };

    public DebitRecordVo completeCardInfo(DebitRecordVo debitRecord,Connection contract){
        StringBuffer querySql = new StringBuffer();
        querySql.append("SELECT card.bank_code, card.ver_code, card.account_no, card.account_name, card.bank_phone, card.bank_branch, card.cert_no");
        querySql.append(" FROM basic_bank_card card");
        querySql.append(" WHERE contract_no = ? AND ver_status = 'alreadySign' and card.del_flag = '0'");
        PreparedStatement queryPs = null;
        ResultSet queryRs = null;
        try {
            queryPs = contract.prepareStatement(querySql.toString());
            queryPs.setString(1,debitRecord.getContractNo());
            queryRs = queryPs.executeQuery();
            if(EmptyUtils.isNotEmpty(queryRs) && queryRs.next()){
                debitRecord.setDebitSignBank(queryRs.getString("bank_code"));
                debitRecord.setDebitSignCode(queryRs.getString("ver_code"));
                debitRecord.setDebitAccount(queryRs.getString("account_no"));
                debitRecord.setDebitAccountName(queryRs.getString("account_name"));
                debitRecord.setDebitCardPhone(queryRs.getString("bank_phone"));
                debitRecord.setDebitBankBranch(queryRs.getString("bank_branch"));
                debitRecord.setDebitCertNo(queryRs.getString("cert_no"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            clean(queryPs);
            clean(queryRs);
        }
        return debitRecord;
    };

    public Map<String,Map<String,String>> getAllCard(Connection contract){
        Map<String,Map<String,String>> resultMap = new HashMap<>(16);
        StringBuffer querySql = new StringBuffer();
        querySql.append("SELECT card.contract_no, card.bank_code, card.ver_code, card.account_no, card.account_name, card.bank_phone, card.bank_branch, card.cert_no");
        querySql.append(" FROM basic_bank_card card");
        querySql.append(" WHERE ver_status = 'alreadySign' and card.del_flag = '0'");
        PreparedStatement queryPs = null;
        ResultSet queryRs = null;
        try {
            queryPs = contract.prepareStatement(querySql.toString());
            queryRs = queryPs.executeQuery();
            if (EmptyUtils.isNotEmpty(queryRs)){
                ResultSetMetaData rmd = queryRs.getMetaData();
                int columnCount = rmd.getColumnCount();
                while (queryRs.next()){
                    Map<String, String> card = new HashMap<String, String>(columnCount);
                    for (int i = 1; i <= columnCount; ++i) {
                        /**  <字段名-字段值> by ZC.GUO  **/
                        card.put(rmd.getColumnName(i), queryRs.getString(i));
                    }
                    /**  获取到了一条记录，map by ZC.GUO  **/
                    String contractNo = queryRs.getString("contract_no");
                    if (EmptyUtils.isNotEmpty(contractNo)){
                        resultMap.put(contractNo,card);
                    }
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
            clean(queryPs);
            clean(queryRs);
        }
        return resultMap;
    };

    public void saveRecordList(List<DebitRecordVo> debitRecordVoList,Connection contract) {
        if (EmptyUtils.isEmpty(debitRecordVoList)){
            return;
        }
        StringBuffer insertSql = new StringBuffer();
        insertSql.append(
            "insert into basic_debit_record(id,contract_no,fee_type,source_table,source_id,ready_amt,succeed_amt," +
                    "going_amt,debit_status,term_no,revision,write_off_amt,plan_date,record_date,manual_num," +
                    "overdue_days,debit_sign_bank,debit_sign_code,debit_account,debit_account_name," +
                    "debit_card_phone,debit_bank_branch,debit_cert_no,create_by,create_time)");
        insertSql.append("values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'system',now())");
        PreparedStatement insertPs = null;
        try {
            insertPs = contract.prepareStatement(insertSql.toString());
            /**  保存剩下的 by ZC.GUO  **/
            if (EmptyUtils.isNotEmpty(debitRecordVoList)){
                this.insetDebitRecord(insertPs,debitRecordVoList);
                contract.commit();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new AfsBaseException(e.getMessage());
        }finally {
            clean(insertPs);
        }
    }

    public void insetDebitRecord(PreparedStatement insertPs,List<DebitRecordVo> debitRecordVoList) throws SQLException {
        if (EmptyUtils.isNotEmpty(debitRecordVoList)){
            for (DebitRecordVo debitRecordVo:debitRecordVoList){
                insertPs.setLong(1,debitRecordVo.getId());
                insertPs.setString(2,debitRecordVo.getContractNo());
                insertPs.setString(3,debitRecordVo.getFeeType());
                insertPs.setString(4,debitRecordVo.getSourceTable());
                insertPs.setLong(5,debitRecordVo.getSourceId());
                insertPs.setBigDecimal(6,debitRecordVo.getReadyAmt());
                insertPs.setBigDecimal(7,debitRecordVo.getSucceedAmt());
                insertPs.setBigDecimal(8,debitRecordVo.getGoingAmt());
                insertPs.setString(9,debitRecordVo.getDebitStatus());

                if (EmptyUtils.isNotEmpty(debitRecordVo.getTermNo())){
                    insertPs.setInt(10,debitRecordVo.getTermNo());
                }else {
                    insertPs.setNull(10,Types.INTEGER);
                }

                insertPs.setLong(11,debitRecordVo.getRevision());
                insertPs.setBigDecimal(12,debitRecordVo.getWriteOffAmt());

                if (EmptyUtils.isNotEmpty(debitRecordVo.getPlanDate())){
                    insertPs.setDate(13,debitRecordVo.getPlanDate());
                }else {
                    insertPs.setNull(13,Types.DATE);
                }

                insertPs.setDate(14,debitRecordVo.getRecordDate());
                insertPs.setInt(15,debitRecordVo.getManualNum());

                if (EmptyUtils.isNotEmpty(debitRecordVo.getOverdueDays())){
                    insertPs.setInt(16,debitRecordVo.getOverdueDays());
                }else {
                    insertPs.setNull(16,Types.INTEGER);
                }

                insertPs.setString(17,debitRecordVo.getDebitSignBank());

                if (EmptyUtils.isNotEmpty(debitRecordVo.getDebitSignCode())){
                    insertPs.setString(18,debitRecordVo.getDebitSignCode());
                }else {
                    insertPs.setNull(18,Types.VARCHAR);
                }

                insertPs.setString(19,debitRecordVo.getDebitAccount());
                insertPs.setString(20,debitRecordVo.getDebitAccountName());

                if (EmptyUtils.isNotEmpty(debitRecordVo.getDebitCardPhone())){
                    insertPs.setString(21,debitRecordVo.getDebitCardPhone());
                }else {
                    insertPs.setNull(21,Types.VARCHAR);
                }

                if (EmptyUtils.isNotEmpty(debitRecordVo.getDebitBankBranch())){
                    insertPs.setString(22,debitRecordVo.getDebitBankBranch());
                }else {
                    insertPs.setNull(22,Types.VARCHAR);
                }

                insertPs.setString(23,debitRecordVo.getDebitCertNo());
                insertPs.addBatch();
            }
        }
        insertPs.executeBatch();
    }
}
